package com.cennavi.minemap.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import com.cennavi.minemap.bean.LayerBean;
import com.cennavi.minemap.bean.LayerPrefixBean;
import com.cennavi.minemap.bean.LayerPerfixTreeBean;
import com.cennavi.minemap.bean.LayerPrefixTreeWithLayerBean;
import com.cennavi.minemap.bean.LayerSortBean;
import com.cennavi.minemap.bean.LayerWithPrefixBean;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class LayerDao extends AbstractDao {

    public int insert(final LayerBean layerBean) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            final String sql = "INSERT INTO MM_LAYER (id, name, describe, soluId, sourceId, content, zindex, dataType, sourceMinZoom, sourceMaxZoom, groupid, groupname, prefix) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, layerBean.getId());
                    ps.setString(2, layerBean.getName());
                    ps.setString(3, layerBean.getDescribe());
                    ps.setInt(4, layerBean.getSoluId());
                    ps.setInt(5, layerBean.getSourceId());
                    ps.setString(6, layerBean.getContent());
                    ps.setInt(7, layerBean.getZindex());
                    ps.setString(8, layerBean.getDataType());
                    ps.setDouble(9, layerBean.getSourceMinZoom());
                    ps.setDouble(10, layerBean.getSourceMaxZoom());
                    ps.setString(11, layerBean.getGroupId() == null ? "" : layerBean.getGroupId());
                    ps.setString(12, layerBean.getGroupName() == null ? "" : layerBean.getGroupName());
                    ps.setString(13, layerBean.getPrefix());
                }
            });

            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int insert(final LayerWithPrefixBean layerBean) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            final String sql = "INSERT INTO MM_LAYER (id, name, describe, soluId, sourceId, content, zindex, dataType, sourceMinZoom, sourceMaxZoom, groupid, groupname, prefix) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, layerBean.getId());
                    ps.setString(2, layerBean.getName());
                    ps.setString(3, layerBean.getDescribe());
                    ps.setInt(4, layerBean.getSoluId());
                    ps.setInt(5, layerBean.getSourceId());
                    ps.setString(6, layerBean.getContent());
                    ps.setInt(7, layerBean.getZindex());
                    ps.setString(8, layerBean.getDataType());
                    ps.setDouble(9, layerBean.getSourceMinZoom());
                    ps.setDouble(10, layerBean.getSourceMaxZoom());
                    ps.setString(11, layerBean.getGroupId() == null ? "" : layerBean.getGroupId());
                    ps.setString(12, layerBean.getGroupName() == null ? "" : layerBean.getGroupName());
                    ps.setString(13, layerBean.getPrefixCode());
                }
            });

            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int update(final LayerBean layerBean) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            String setSql = super.getSetSql(layerBean);
            String sql = "UPDATE MM_LAYER SET " + setSql + " WHERE id = ?";
            jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, layerBean.getId());
                }
            });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int updateStyle(final String id, final String content) {
        try {
            jdbcTemplate.setDataSource(pgDataSource);
            String sql = "UPDATE MM_LAYER SET content = ? WHERE id = ?";
            jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, content);
                    ps.setString(2, id);
                }
            });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int updateLayerSort(List<LayerSortBean> layerSort) {
        try {
            jdbcTemplate.setDataSource(pgDataSource);

            jdbcTemplate.execute("begin");

            String sql = "UPDATE MM_LAYER SET zindex = ? WHERE id = ?";
            for (LayerSortBean layerSortBean : layerSort) {
                jdbcTemplate.update(sql, new Object[] { layerSortBean.getZindex(), layerSortBean.getId() });
            }
            jdbcTemplate.execute("commit");
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int delete(String id) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            String sql = "DELETE FROM MM_LAYER WHERE id = ?";
            jdbcTemplate.update(sql, new Object[] { id }, new int[] { java.sql.Types.VARCHAR });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int deleteBySoluId(int soluId) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            String sql = "DELETE FROM MM_LAYER WHERE soluId = ?";
            jdbcTemplate.update(sql, new Object[] { soluId }, new int[] { java.sql.Types.INTEGER });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public List<LayerWithPrefixBean> queryBySoluId(int soluId) {
        jdbcTemplate.setDataSource(pgDataSource);

        String sql = "SELECT L.ID, L.NAME, P.CODE AS PREFIXCODE,P.NAME AS PREFIXNAME, "
                + "L.DESCRIBE, L.SOLUID, L.SOURCEID, L.CONTENT, L.ZINDEX, "
                + "L.DATATYPE, L.SOURCEMINZOOM, L.SOURCEMAXZOOM, L.GROUPID, "
                + "L.GROUPNAME FROM MM_LAYER L LEFT JOIN MM_LAYER_PREFIX P "
                + "ON L.PREFIX = P.CODE WHERE SOLUID = ? ORDER BY ZINDEX";

        return (List<LayerWithPrefixBean>) jdbcTemplate.query(sql, new Object[] { soluId },
                new LayerWithPrefixMapper());
    }

    public List<LayerBean> queryBySoluIdAndPrefix(int soluId, String prefix, int isPublish) {
        jdbcTemplate.setDataSource(pgDataSource);
        String tableName = "MM_LAYER";
        if (isPublish == 1) {
            tableName = "MM_LAYER_PUBLISH";
        } else {
            tableName = "MM_LAYER";
        }

        String sql = "SELECT L.ID, L.NAME, " + "L.DESCRIBE, L.SOLUID, L.SOURCEID, L.CONTENT, L.ZINDEX, "
                + "L.DATATYPE, L.SOURCEMINZOOM, L.SOURCEMAXZOOM, L.GROUPID, " + "L.GROUPNAME FROM " + tableName
                + " L WHERE SOLUID = ? AND prefix = ? ORDER BY ZINDEX";

        return (List<LayerBean>) jdbcTemplate.query(sql, new Object[] { soluId, prefix }, new LayerMapper());
    }

    public List<LayerBean> queryUploadSourceByUserId(int userId) {
        jdbcTemplate.setDataSource(pgDataSource);
        String source = userId + " upload";
        String sql = "SELECT L.ID, L.NAME, L.DESCRIBE, L.SOLUID, L.SOURCEID, L.CONTENT, "
                + "L.ZINDEX, L.DATATYPE, L.SOURCEMINZOOM, L.SOURCEMAXZOOM, L.GROUPID, "
                + "L.GROUPNAME FROM MM_LAYER L WHERE EXISTS (SELECT 1 FROM MM_DATASOURCE_UPLOAD D "
                + "WHERE L.SOURCEID = D.ID AND SOURCE = ?) "
                + "AND EXISTS (SELECT 1 FROM mm_solution S WHERE S.USERID = ? AND S.ID = L.SOLUID);";
        return (List<LayerBean>) jdbcTemplate.query(sql, new Object[] { source, userId }, new LayerMapper());
    }

    public int deletePublishBySoluId(int soluId) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            String sql = "DELETE FROM MM_LAYER_PUBLISH WHERE soluId = ?";
            jdbcTemplate.update(sql, new Object[] { soluId }, new int[] { java.sql.Types.INTEGER });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int copyToPublish(final int soluId) {
        jdbcTemplate.setDataSource(pgDataSource);
        try {
            String sql = "INSERT INTO MM_LAYER_PUBLISH SELECT * FROM mm_layer l WHERE l.soluid = ?";
            jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setInt(1, soluId);
                }
            });
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    /**
     * 需求变更 2017-03-16 查询发布后的表 by cuiliang
     * 
     * @param soluId
     * @return
     */
    public List<LayerWithPrefixBean> queryPublishBySoluId(int soluId) {
        jdbcTemplate.setDataSource(pgDataSource);
        String sql = "SELECT L.ID, L.NAME, P.CODE AS PREFIXCODE,P.NAME AS PREFIXNAME, "
                + "L.DESCRIBE, L.SOLUID, L.SOURCEID, L.CONTENT, L.ZINDEX, "
                + "L.DATATYPE, L.SOURCEMINZOOM, L.SOURCEMAXZOOM, L.GROUPID, "
                + "L.GROUPNAME FROM MM_LAYER_PUBLISH L LEFT JOIN MM_LAYER_PREFIX P "
                + "ON L.PREFIX = P.CODE WHERE SOLUID = ? ORDER BY ZINDEX";

        return (List<LayerWithPrefixBean>) jdbcTemplate.query(sql, new Object[] { soluId },
                new LayerWithPrefixMapper());
    }

    public LayerWithPrefixBean queryById(String id) {
        jdbcTemplate.setDataSource(pgDataSource);
        String sql = "SELECT L.ID, L.NAME, P.CODE AS PREFIXCODE,P.NAME AS PREFIXNAME, "
                + "L.DESCRIBE, L.SOLUID, L.SOURCEID, L.CONTENT, L.ZINDEX, "
                + "L.DATATYPE, L.SOURCEMINZOOM, L.SOURCEMAXZOOM, L.GROUPID, "
                + "L.GROUPNAME FROM MM_LAYER L LEFT JOIN MM_LAYER_PREFIX P " + "ON L.PREFIX = P.CODE WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[] { id }, new LayerWithPrefixMapper());
    }

    public int updateGroup(final String groupId, final String groupName, String[] ids) {
        try {
            jdbcTemplate.setDataSource(pgDataSource);
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("ids", Arrays.asList(ids));
            parameters.addValue("groupId", groupId);
            parameters.addValue("groupName", groupName);
            String sql = "UPDATE MM_LAYER SET groupid = :groupId, groupname= :groupName WHERE id IN (:ids)";
            namedParameterJdbcTemplate.update(sql, parameters);
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    protected class LayerMapper implements RowMapper<LayerBean> {
        public LayerBean mapRow(ResultSet rs, int rowNum) throws SQLException {
            LayerBean layer = new LayerBean();
            layer.setId(rs.getString("id"));
            layer.setName(rs.getString("name"));
            layer.setDescribe(rs.getString("describe"));
            layer.setSoluId(rs.getInt("soluId"));
            layer.setSourceId(rs.getInt("sourceId"));
            layer.setContent(rs.getString("content"));
            layer.setZindex(rs.getInt("zindex"));
            layer.setDataType(rs.getString("dataType"));
            layer.setSourceMinZoom(rs.getDouble("sourceMinZoom"));
            layer.setSourceMaxZoom(rs.getDouble("sourceMaxZoom"));
            layer.setGroupId(rs.getString("groupId"));
            layer.setGroupName(rs.getString("groupName"));
            return layer;
        }
    }

    protected class LayerWithPrefixMapper implements RowMapper<LayerWithPrefixBean> {
        public LayerWithPrefixBean mapRow(ResultSet rs, int rowNum) throws SQLException {
            LayerWithPrefixBean layer = new LayerWithPrefixBean();
            layer.setId(rs.getString("id"));
            layer.setPrefixCode(rs.getString("prefixCode"));
            layer.setPrefixName(rs.getString("prefixName"));
            layer.setName(rs.getString("name"));
            layer.setDescribe(rs.getString("describe"));
            layer.setSoluId(rs.getInt("soluId"));
            layer.setSourceId(rs.getInt("sourceId"));
            layer.setContent(rs.getString("content"));
            layer.setZindex(rs.getInt("zindex"));
            layer.setDataType(rs.getString("dataType"));
            layer.setSourceMinZoom(rs.getDouble("sourceMinZoom"));
            layer.setSourceMaxZoom(rs.getDouble("sourceMaxZoom"));
            layer.setGroupId(rs.getString("groupId"));
            layer.setGroupName(rs.getString("groupName"));
            return layer;
        }
    }

    public List<LayerPerfixTreeBean> queryLayerPerfixTreeByCode(String code) {
        jdbcTemplate.setDataSource(pgDataSource);
        List<LayerPerfixTreeBean> list = new ArrayList<LayerPerfixTreeBean>();
        String sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE code = ? order by code";
        List<LayerPrefixBean> LayerPrefixBeans = jdbcTemplate.query(sql, new Object[] { code },
                new LayerPerfixMapper());
        for (LayerPrefixBean LayerPrefixBean : LayerPrefixBeans) {
            LayerPerfixTreeBean layerPerfixTreeBean = new LayerPerfixTreeBean();
            layerPerfixTreeBean.setCode(LayerPrefixBean.getCode());
            layerPerfixTreeBean.setName(LayerPrefixBean.getName());
            layerPerfixTreeBean.setIsleaf(LayerPrefixBean.getIsleaf());
            layerPerfixTreeBean.setContent(LayerPrefixBean.getContent());
            layerPerfixTreeBean.setType(LayerPrefixBean.getType());
            if (LayerPrefixBean.getIsleaf() == 0) {
                layerPerfixTreeBean.setChildren(queryLayerPerfixTreeByFather(LayerPrefixBean.getCode()));
            }
            list.add(layerPerfixTreeBean);
        }
        return list;
    }

    public List<LayerPrefixBean> queryLayerPerfixTreeByType(Integer type) {
        jdbcTemplate.setDataSource(pgDataSource);
        String sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE type = ? order by code";
        List<LayerPrefixBean> LayerPrefixBeans = jdbcTemplate.query(sql, new Object[] { type },
                new LayerPerfixMapper());
        return LayerPrefixBeans;
    }

    // public List<LayerPrefixBean> queryLayerPerfix() {
    // jdbcTemplate.setDataSource(pgDataSource);
    // String sql = "SELECT code, name, father, isleaf FROM MM_LAYER_PREFIX
    // ORDER BY code";
    // return jdbcTemplate.query(sql, new LayerPerfixMapper());
    // }

    public List<LayerPerfixTreeBean> queryLayerPerfixTreeByFather(String father) {
        jdbcTemplate.setDataSource(pgDataSource);
        List<LayerPerfixTreeBean> list = new ArrayList<LayerPerfixTreeBean>();

        String sql = null;
        List<LayerPrefixBean> LayerPrefixBeans = null;

        if (father == null) {
            sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE father is null order by code";
            LayerPrefixBeans = jdbcTemplate.query(sql, new LayerPerfixMapper());
        } else {
            sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE father = ? order by code";
            LayerPrefixBeans = jdbcTemplate.query(sql, new Object[] { father }, new LayerPerfixMapper());
        }

        for (LayerPrefixBean LayerPrefixBean : LayerPrefixBeans) {
            LayerPerfixTreeBean layerPerfixTreeBean = new LayerPerfixTreeBean();
            layerPerfixTreeBean.setCode(LayerPrefixBean.getCode());
            layerPerfixTreeBean.setName(LayerPrefixBean.getName());
            layerPerfixTreeBean.setIsleaf(LayerPrefixBean.getIsleaf());
            layerPerfixTreeBean.setContent(LayerPrefixBean.getContent());
            layerPerfixTreeBean.setType(LayerPrefixBean.getType());
            if (LayerPrefixBean.getIsleaf() == 0) {
                layerPerfixTreeBean.setChildren(queryLayerPerfixTreeByFather(LayerPrefixBean.getCode()));
            }
            list.add(layerPerfixTreeBean);
        }
        return list;
    }

    public List<LayerPrefixTreeWithLayerBean> queryLayerPerfixTreeWithLaByFather(String father, int soluId,
            int isPublish, int type) {
        jdbcTemplate.setDataSource(pgDataSource);
        List<LayerPrefixTreeWithLayerBean> list = new ArrayList<LayerPrefixTreeWithLayerBean>();

        String sql = null;
        List<LayerPrefixBean> LayerPrefixBeans = null;

        if (father == null) {
            sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE father is null AND type = ? order by code";
            LayerPrefixBeans = jdbcTemplate.query(sql, new Object[] { type }, new LayerPerfixMapper());
        } else {
            sql = "SELECT code, name, father, isleaf, content, type FROM MM_LAYER_PREFIX WHERE father = ? AND type = ? order by code";
            LayerPrefixBeans = jdbcTemplate.query(sql, new Object[] { father, type }, new LayerPerfixMapper());
        }

        for (LayerPrefixBean LayerPrefixBean : LayerPrefixBeans) {
            LayerPrefixTreeWithLayerBean layerPerfixTreeBean = new LayerPrefixTreeWithLayerBean();
            layerPerfixTreeBean.setCode(LayerPrefixBean.getCode());
            layerPerfixTreeBean.setName(LayerPrefixBean.getName());
            layerPerfixTreeBean.setIsleaf(LayerPrefixBean.getIsleaf());
            layerPerfixTreeBean.setContent(LayerPrefixBean.getContent());
            if (LayerPrefixBean.getIsleaf() == 0) {
                layerPerfixTreeBean.setChildren(
                        queryLayerPerfixTreeWithLaByFather(LayerPrefixBean.getCode(), soluId, isPublish, type));
            } else {
                layerPerfixTreeBean.setLayers(queryBySoluIdAndPrefix(soluId, LayerPrefixBean.getCode(), isPublish));
            }
            list.add(layerPerfixTreeBean);
        }
        return list;
    }

    public int updateLayersPrefix(final String prefix, String[] ids) {
        try {
            jdbcTemplate.setDataSource(pgDataSource);
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("prefix", prefix);
            parameters.addValue("ids", Arrays.asList(ids));
            String sql = "UPDATE mm_layer SET prefix = :prefix WHERE id IN (:ids)";
            namedParameterJdbcTemplate.update(sql, parameters);
            return 0;
        } catch (Exception e) {
            return -1;
        }
    }

    protected class LayerPerfixMapper implements RowMapper<LayerPrefixBean> {
        public LayerPrefixBean mapRow(ResultSet rs, int rowNum) throws SQLException {
            LayerPrefixBean perfix = new LayerPrefixBean();
            perfix.setCode(rs.getString("code"));
            perfix.setName(rs.getString("name"));
            perfix.setFather(rs.getString("father"));
            perfix.setIsleaf(rs.getInt("isleaf"));
            perfix.setContent(rs.getString("content"));
            perfix.setType(rs.getInt("type"));
            return perfix;
        }
    }
}
